﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace HY.BLL
{
   public class Bllbase
    {
        /// <summary>
        /// 试 用版限定,300行记录
        /// </summary>
        public static bool check_rec(string tab)
        {
            string sqltxt = "select * from " + tab + "  ";
            DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
            if (dt.Rows.Count > 300)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 检测是否收藏客户
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static Boolean chk_comp_fav(string cbh, string sbh)
        {
            string sqltxt = "select sid from [company_fav] where cbh='" + cbh.Trim() + "' and sbh='" + sbh.Trim() + "' ";
            DataTable dte = DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
            if (dte.Rows.Count > 0)
                return true;
            else
                return false;
        }
    
       //取城市
       public static string getcity(string val)
       {
           DataTable dte = BLL.Bllbase.get_table("[pub_area]");
           if (dte.Rows.Count > 0)
               return dte.Rows[0]["svalue"].ToString();
           else
               return "";

       }

       //取管理员
       public static string getrole(string val)
       {
           DataTable dte = BLL.Bllbase.get_table("[user_safe]"," ubh='"+val+"' ");

           if (dte.Rows.Count > 0)
               return dte.Rows[0]["mname"].ToString();
           else
               return "";

       }


       /// <summary>
       /// 加条件
       /// </summary>
       /// <param name="table"></param>
       /// <param name="whe"></param>
       /// <returns></returns>
       public static DataTable get_sql(string sql, string whe, string gp, string ord)
       {
           string sqltxt = sql.Trim();

           if (whe != "")
           {

               whe = whe.Trim();
               int f = whe.IndexOf("and");

               if (f >= 0 & f <= 3)
                   sqltxt = sqltxt + " where 1=1  " + whe.Trim();
               else
                   sqltxt = sqltxt + " where 1=1 and " + whe.Trim();

           }

           if (gp != "")
           {
               sqltxt = sqltxt + gp;           
           }


           if (ord != "")
           {
               sqltxt = sqltxt + ord;
           }

           return DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
       }


       /// <summary>
       /// 取表通过表名
       /// </summary>
       /// <param name="table"></param>
       /// <returns></returns>
       public static DataTable get_table(string table)
       {
           string sqltxt = "select * from " + table.Trim() + " ";
           return DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
       }



       /// <summary>
       /// 加条件
       /// </summary>
       /// <param name="table"></param>
       /// <param name="whe"></param>
       /// <returns></returns>
       public static DataTable get_table(string table, string whe)
       {
           string sqltxt = "select * from " + table.Trim();

           if (whe != "")
           {

               whe = whe.Trim();
               int f = whe.IndexOf("and");

               if (f >= 0 & f <= 3)
                   sqltxt = sqltxt + " where 1=1  " + whe.Trim();
               else
                   sqltxt = sqltxt + " where 1=1 and " + whe.Trim();

           }

           return DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
       }

       /// <summary>
       /// 加排序
       /// </summary>
       /// <param name="table"></param>
       /// <param name="whe"></param>
       /// <param name="ord"></param>
       /// <returns></returns>
       public static DataTable get_table(string table, string whe,string ord)
       {
           string sqltxt = "select * from " + table.Trim();

           if (whe != "")
           {

               whe = whe.Trim();
               int f = whe.IndexOf("and");

               if (f >= 0 & f <= 3)
                   sqltxt = sqltxt + " where 1=1  " + whe.Trim();
               else
                   sqltxt = sqltxt + " where 1=1 and " + whe.Trim();

           }
           if (ord!="")
               sqltxt = sqltxt +"  "+ ord;
           
           return DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
       }

 /// <summary>
 /// 加分页功能GETTABBLE
 /// </summary>
 /// <param name="table">表名</param>
 /// <param name="whe">条件不带and</param>
 /// <param name="ord">排序，不带order by</param>
 /// <param name="pgindex">当前页</param>
 /// <param name="pgsize">页大小</param>
 /// <returns></returns>
       public static DataTable get_table(string table, string whe, string ord,int pgindex,int pgsize)
       {
           if (whe.Length > 0)
           {
               if (whe.IndexOf("and") < 0 || whe.IndexOf("and") > 3) whe ="  and "+  whe ;
           }

           if (pgindex == 0) pgindex = 1;
           if (pgsize == 0) pgsize = 15;

           ord = ord.Replace("order by","  ");

           string sqltxt = "EXEC ShowPage  N' * from "+table+" where 1=1 "+whe+" ', N'"+ord+"', "+pgsize+","+pgindex+"";
          // string sqltxt = "select * from " + table.Trim();


           return DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
       }

       public static int page_count(string table, string whe)
       {
           int val =0;
           string sqltxt = "select count(sid) from " + table.Trim();
           if (whe != "") sqltxt = sqltxt + " where 1=1 and " + whe.Trim();
           DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
           if (dt.Rows.Count > 0)
           {
               val =int.Parse(dt.Rows[0][0].ToString());
           }

           return val;
       }



       public static string get_val(string table,string filed ,string whe)
       {
           string val = "";
           string sqltxt = "select " + filed.Trim() + " from " + table.Trim();
           if (whe != "") sqltxt = sqltxt + " where 1=1 and " + whe.Trim();
           DataTable dt=DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
           if (dt.Rows.Count > 0)
           { 
            val= dt.Rows[0][0].ToString();               
           }

           return val;
       }
       /// <summary>
       /// 检测一条数据
       /// </summary>
       public static bool CheckList(string tab, string sidlist)
       {
           string sqltxt = "select * from  " + tab + " where owenid in(" + sidlist + ") ";
           DataTable dt=DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
           if (dt.Rows.Count > 0)
               return true;
           else
               return false;
       }

       /// <summary>
       /// 删除一条数据
       /// </summary>
       public static int DeleteList(string tab,string sidlist)
       {
           string sqltxt = "delete "+tab+" where sid in(" + sidlist + ") ";
           return DAL.DbHelperSQL.ExecuteNonQuery(sqltxt);
       }

       /// <summary>
       /// 清理一条数据
       /// </summary>
       public static int ClearList(string tab, string sidlist)
       {
           string sqltxt = "update company set temp=memo,memo='' where sid in(" + sidlist + ") ";
           return DAL.DbHelperSQL.ExecuteNonQuery(sqltxt);
       }

       /// <summary>
       /// 删除一条数据
       /// </summary>
       public static int DeleteList(string tab, string sidlist,string whe)
       {
           string sqltxt = "delete " + tab + " where sid in(" + sidlist + ") ";
           if (whe != "") sqltxt = sqltxt + " and " + whe;

           return DAL.DbHelperSQL.ExecuteNonQuery(sqltxt);
       }


       /// <summary>
       /// 更新业务员操作记录
       /// </summary>
       public static int update_rec(string tab,string val, string whe)
       {
           if (whe == "")
               return 0;
           else
           {
               if (whe.IndexOf("and") < 0) whe = " and " + whe;
           } 
           string sqltxt = "update  " + tab + " set  updtime=getdate(), sales='"+val+"' where 1=1 "+whe;

        
           return DAL.DbHelperSQL.ExecuteNonQuery(sqltxt);
       }


       /// <summary>
       /// 检测数据是否存在
       /// </summary>
       public static bool check_exits(string tab, string whe)
       {
           string sqltxt = "select * from " + tab + " where " + whe + "  ";
           DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
           if (dt.Rows.Count > 0)
               return true;
           else
               return false;
       }


     
       public static string get_table_count(string sales, string stype,string tab,string sdt,string edt)
       {
           
           string sqltxt = "",val="";
           if (stype == "1")
           {
               if (tab == "company")
                   sqltxt = " select sales,count(sid) as cnum from company where sales='" + sales + "' ";

               if (tab == "school")
                   sqltxt = " select sales,count(sid) as cnum from school where sales='" + sales + "' ";

               if (tab == "zj")
                   sqltxt = " select sales,count(sid) as cnum from zj where sales='" + sales + "' ";
           }

           if (stype == "2")
           {
               if (tab == "company")
                   sqltxt = " select sales,count(sid) as cnum from company where cbh in(select cbh from company_fav) and sales='" + sales + "' ";

               if (tab == "school")
                   sqltxt = " select sales,count(sid) as cnum from school where schbh in(select cbh from company_fav) and  sales='" + sales + "' ";

               if (tab == "zj")
                   sqltxt = " select sales,count(sid) as cnum from zj where zbh in(select cbh from company_fav) and  sales='" + sales + "' ";
           }

           if (sdt!="" & edt!="")
           {
               sqltxt = sqltxt + " and  convert(char(10),updtime,120)>=convert(char(10),convert(datetime,'" + sdt + "'),120) " +
                         " and  convert(char(10),updtime,120)<=convert(char(10),convert(datetime,'" + edt + "'),120) ";            
           }

           sqltxt = sqltxt + " group by sales order by sales";

           if (stype == "3")
           {
               sqltxt = " select pname,count(sid) as cnum from comp_tel where pname='" + sales + "' and left(cbh,2)='" + tab + "'  ";


               if (sdt != "" & edt != "")
               {
                   sqltxt = sqltxt + " and  convert(char(10),sdtime,120)>=convert(char(10),convert(datetime,'" + sdt + "'),120) " +
                             " and  convert(char(10),sdtime,120)<=convert(char(10),convert(datetime,'" + edt + "'),120) ";
               }

               sqltxt = sqltxt + " group by pname";
           }

           DataTable dp = DAL.DbHelperSQL.SqlDataAdapter(sqltxt);
           if (dp.Rows.Count>0) val= dp.Rows[0]["cnum"].ToString();


           return val;
       }

       /// <summary>
       /// 设定列表,所有菜单
       /// </summary>
       /// <param name="sid"></param>
       /// <returns></returns>
       public static DataTable Get_Menu_safe(string sid)
       {

           string bmenu = BLL.Bllbase.get_val("menu_safe", "bmenu", "sid=" + sid + "");

           if (bmenu == "") bmenu = "-1";

           string strsql = " exec ('SELECT  [sid] ,[owenid],[lev],[m_name] ,[m_link]" +
                             "  ,[showstatus] ,[isstatus] ,[isorder] ,[remark] ,[TreeLevel]" +
                              " ,(CASE WHEN sid IN (" + bmenu + ") THEN 1 ELSE 0 END) as Atvalid FROM [sys_menu]  order by remark,TreeLevel ')";



           DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(strsql);

           return dt;
       }

       /// <summary>
       /// 读取菜单,有权限,可显示的
       /// </summary>
       /// <param name="sid"></param>
       /// <returns></returns>
       public static DataTable Get_Menu_safe_Read(string sid)
       {
           string bmenu = BLL.Bllbase.get_val("menu_safe", "bmenu", "sid=" + sid + "");
           if (bmenu == "") bmenu = "-1";
           string strsql = " exec ('SELECT  [sid] ,[owenid],[lev],[m_name] ,[m_link]" +
                             "  ,[showstatus] ,[isstatus] ,[isorder] ,[remark] ,[TreeLevel]" +
                              "  FROM [sys_menu] where  showstatus=1 and sid IN (" + bmenu + ") order by isorder')";


           DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(strsql);

           return dt;
       }

      
       /// <summary>
       /// 检测页面是否合法,可显示的
       /// </summary>
       /// <param name="sid"></param>
       /// <param name="filename"></param>
       /// <returns></returns>
       public static Boolean Get_Menu_safe_Check(string sid,string filename)
       {
           string bmenu = BLL.Bllbase.get_val("menu_safe", "bmenu", "sid=" + sid + "");
           if (bmenu == "") bmenu = "-1";
           string strsql = " exec ('SELECT  [sid] ,[owenid],[lev],[m_name] ,[m_link]" +
                             "  ,[showstatus] ,[isstatus] ,[isorder] ,[remark] ,[TreeLevel]" +
                              "  FROM [sys_menu] where   sid IN (" + bmenu +
                              ")  and charindex(''" + filename + "'',m_link)>0 order by isorder')";


           DataTable dt = DAL.DbHelperSQL.SqlDataAdapter(strsql);
           if (dt.Rows.Count > 0)
               return true;
           else
               return false;
     
       }

    }
}
